package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.TreeMap;

import javax.sql.DataSource;

import model.vo.MakindVO;
import model.vo.MtkindVO;

public class RiskIjdbcDAOImpl implements RiskIjdbcDAO {
	private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public void closeAll(PreparedStatement pstmt,Connection con) throws SQLException{
		if(pstmt!=null)
			pstmt.close();
		if(con!=null)
			con.close();
	}
	public void closeAll(Statement stmt,Connection con) throws SQLException{
		if(stmt!=null)
			stmt.close();
		if(con!=null)
			con.close();
	}
	public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con) throws SQLException{
		if(rs!=null)
			rs.close();
		closeAll(pstmt,con);
	}

	//(자산위협매트릭스)
	@Override
	public List atmetrix(int pro_id, ArrayList<MakindVO> listMap) throws SQLException {
		ArrayList list=new ArrayList();
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=dataSource.getConnection();
			String sSQL="";
		    sSQL=sSQL+" SELECT ";
		    //sSQL=sSQL+"   A.TK_ID, ";
		    sSQL=sSQL+"   A.BASE_LEV1, ";
		    sSQL=sSQL+"   A.BASE_LEV2, ";
		    sSQL=sSQL+"   CASE WHEN (C.BASE_LEV3='Y') THEN 'Y' ELSE A.BASE_LEV3 END, ";
		    sSQL=sSQL+"   A.TK_NM1, ";
		    sSQL=sSQL+"   A.TK_NM2, ";
		    sSQL=sSQL+"   CASE WHEN (C.BASE_LEV3='Y') THEN '' ELSE A.TK_NM3 END ";
		    if(listMap.size()!=0){
		    	sSQL=sSQL+",";
		    }
		    for(int i=0; i<listMap.size(); i++){
				MakindVO vo=(MakindVO)listMap.get(i);
				sSQL=sSQL+" MAX(CASE WHEN C.AK_ID='"+vo.getBase_lev1()+"' THEN C.AT_VALUE ELSE 0 END) AS ak_id"+vo.getBase_lev1()+" ";
				if((listMap.size()-1)!=i){
					 sSQL=sSQL+",";
				}
			}
		    sSQL=sSQL+" FROM TTKIND A ";
		    sSQL=sSQL+"   LEFT OUTER JOIN TAKIND B ON A.PRO_ID=B.PRO_ID ";
		    sSQL=sSQL+"  LEFT OUTER JOIN PRO_ATMETRIX C ON B.PRO_ID=C.PRO_ID AND " +
		    		"(( A.BASE_LEV1=C.BASE_LEV1 AND A.BASE_LEV2=C.BASE_LEV2 AND A.BASE_LEV3=C.BASE_LEV3 ) OR (  A.BASE_LEV1=C.BASE_LEV1 AND A.BASE_LEV2=C.BASE_LEV2 AND C.BASE_LEV3='Y'))  ";
		    sSQL=sSQL+" WHERE A.PRO_ID=? ";
		 //  sSQL=sSQL+" GROUP BY A.TK_ID, A.BASE_LEV1, A.BASE_LEV2, A.BASE_LEV3, A.TK_NM1, A.TK_NM2, A.TK_NM3 ";
		    sSQL=sSQL+" GROUP BY A.BASE_LEV1, A.BASE_LEV2, " +
		    		"CASE WHEN (C.BASE_LEV3='Y') THEN 'Y' ELSE A.BASE_LEV3 END, A.TK_NM1, A.TK_NM2, " +
		    		"CASE WHEN (C.BASE_LEV3='Y') THEN '' ELSE A.TK_NM3 END ";
		   // System.out.println(sSQL);
			//String sql="select * from barcode where userid=?";
			pstmt=con.prepareStatement(sSQL);
			pstmt.setInt(1, pro_id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				HashMap map=new HashMap();
				//map.put("tk_id",rs.getInt(1));
				map.put("base_lev1",rs.getString(1));
				map.put("base_lev2",rs.getString(2));
				map.put("base_lev3",rs.getString(3));
				map.put("tk_nm1",rs.getString(4));
				map.put("tk_nm2",rs.getString(5));
				map.put("tk_nm3",rs.getString(6));
				 for(int i=0; i<listMap.size(); i++){
					MakindVO vo=(MakindVO)listMap.get(i);
					 map.put("ak_id"+vo.getBase_lev1(), rs.getString("ak_id"+vo.getBase_lev1()));
				 }
				 //System.out.println("맵:"+map);
				 TreeMap tm = new TreeMap();
				 tm.putAll(map);
				 list.add(tm);
				//list.add(new ScmVO(rs.getString("bal_date"),rs.getString("bal_no"),rs.getString("p_no"),rs.getString("p_nm"),
				//		rs.getString("p_kd"),rs.getString("p_dan"),rs.getInt("bal_cnt"),rs.getString("ip_date"),rs.getString("bal_ok")));
				}
			}finally{
			closeAll(rs,pstmt,con);
		}
			//System.out.println("리스트:"+list);
		return list;
	}
	
	

	//(위험취약성매트릭스)
	@Override
	public List tvmetrix(int pro_id, ArrayList<MtkindVO> listMap) throws SQLException {
		ArrayList list=new ArrayList();
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=dataSource.getConnection();
			String sSQL="";
		    sSQL=sSQL+" SELECT ";
		    sSQL=sSQL+"   A.TS_CD1, ";
		    sSQL=sSQL+"   A.TS_CD2, ";
		    //sSQL=sSQL+"   CASE WHEN C.TS_CD3='Y' THEN 'Y' ELSE A.TS_CD3 END, ";
		    sSQL=sSQL+"   A.TS_CD3, ";
		    sSQL=sSQL+"   A.TS_CD4, ";
		    sSQL=sSQL+"   A.TS_NM1, ";
		    sSQL=sSQL+"   A.TS_NM2, ";
		    //sSQL=sSQL+"   CASE WHEN C.TS_CD3='Y' THEN '' ELSE A.TS_NM3 END ";
		    sSQL=sSQL+"   A.TS_NM3,";
		    sSQL=sSQL+"   A.TS_NM4";
		    if(listMap.size()!=0){
		    	sSQL=sSQL+",";
		    }
		    for(int i=0; i<listMap.size(); i++){
		    	MtkindVO vo=(MtkindVO)listMap.get(i);
				sSQL=sSQL+" MAX(CASE WHEN C.BASE_LEV1='"+vo.getBase_lev1()+"' AND C.BASE_LEV2='"+vo.getBase_lev2()+"' AND C.BASE_LEV3='"+vo.getBase_lev3()+"' THEN C.TV_VALUE ELSE 0 END) AS tk_id"+vo.getBase_lev1()+""+vo.getBase_lev2()+""+vo.getBase_lev3()+" ";
				if((listMap.size()-1)!=i){
					 sSQL=sSQL+",";
				}
			}
		    sSQL=sSQL+" FROM TVCHECKLIST A ";
		    //sSQL=sSQL+"   LEFT OUTER JOIN TTKIND B ON A.PRO_ID=B.PRO_ID ";
		    sSQL=sSQL+"  LEFT OUTER JOIN PRO_TVMETRIX C ON A.PRO_ID=C.PRO_ID AND " +
		              "  A.TS_CD1=C.TS_CD1 AND A.TS_CD2=C.TS_CD2 AND A.TS_CD3=C.TS_CD3 AND A.TS_CD4=C.TS_CD4";
		    //		"(( A.TS_CD1=C.TS_CD1 AND A.TS_CD2=C.TS_CD2 AND A.TS_CD3=C.TS_CD3) " +
		    //		"OR ( A.TS_CD1=C.TS_CD1 AND A.TS_CD2=C.TS_CD2 AND C.TS_CD3='Y')) ";
		    sSQL=sSQL+" WHERE A.PRO_ID=? ";
		    //sSQL=sSQL+" GROUP BY A.TS_CD1, A.TS_CD2, CASE WHEN C.TS_CD3='Y' THEN 'Y' ELSE A.TS_CD3 END, A.TS_NM1, A.TS_NM2, CASE WHEN C.TS_CD3='Y' THEN '' ELSE A.TS_NM3 END ";
		    sSQL=sSQL+" GROUP BY A.TS_CD1, A.TS_CD2,A.TS_CD3,A.TS_CD4, A.TS_NM1, A.TS_NM2, A.TS_NM3, A.TS_NM4 ";
		    System.out.println(sSQL);
			//String sql="select * from barcode where userid=?";
			pstmt=con.prepareStatement(sSQL);
			pstmt.setInt(1, pro_id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				HashMap map=new HashMap();
				map.put("ts_cd1",rs.getString(1));
				map.put("ts_cd2",rs.getString(2));
				map.put("ts_cd3",rs.getString(3));
				map.put("ts_cd4",rs.getString(4));
				map.put("ts_nm1",rs.getString(5));
				map.put("ts_nm2",rs.getString(6));
				map.put("ts_nm3",rs.getString(7));
				map.put("ts_nm4",rs.getString(8));
				 for(int i=0; i<listMap.size(); i++){
					 MtkindVO vo=(MtkindVO)listMap.get(i);
					 map.put("tk_id"+vo.getBase_lev1()+""+vo.getBase_lev2()+""+vo.getBase_lev3(), rs.getString("tk_id"+vo.getBase_lev1()+""+vo.getBase_lev2()+""+vo.getBase_lev3()));
				 }
				 //System.out.println("맵:"+map);
				 TreeMap tm = new TreeMap();
				 tm.putAll(map);
				 list.add(tm);
				//list.add(new ScmVO(rs.getString("bal_date"),rs.getString("bal_no"),rs.getString("p_no"),rs.getString("p_nm"),
				//		rs.getString("p_kd"),rs.getString("p_dan"),rs.getInt("bal_cnt"),rs.getString("ip_date"),rs.getString("bal_ok")));
				}
			}finally{
			closeAll(rs,pstmt,con);
		}
			//System.out.println("리스트:"+list);
		return list;
	}
	
	//(자산현황)
	@Override
	public List mastate(int pro_id, ArrayList<MakindVO> listMap) throws SQLException {
		ArrayList list=new ArrayList();
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=dataSource.getConnection();
			String sSQL="";
			sSQL=sSQL+" SELECT ";
			sSQL=sSQL+" 	ZZ.Pro_Id, ";
			sSQL=sSQL+" 	ZZ.Rainfo_Id, ";
			sSQL=sSQL+" 	ZZ.Rainfo_Nm, ";
			sSQL=sSQL+" 	ZZ.Racont_Nm, ";
			sSQL=sSQL+" 	ZZ.Base_Lev1, ";
			sSQL=sSQL+" 		(SELECT COUNT(*)+1 ";
			sSQL=sSQL+" 			FROM ( SELECT A.Pro_Id ,A.Rainfo_Id,A.Rainfo_Nm,B.Racont_Nm ,B.Base_Lev1 ";
			sSQL=sSQL+" 							FROM TRAINFO A LEFT OUTER JOIN TRACONT1 B ON A.Pro_Id=B.Pro_Id AND A.Rainfo_id=B.Rainfo_Id ";
			sSQL=sSQL+" 						 WHERE A.Pro_Id=? ";
			sSQL=sSQL+" 						 UNION ALL";
			sSQL=sSQL+" 						SELECT A.Pro_Id ,A.Rainfo_Id,A.Rainfo_Nm,B.Racont_Nm AS Racont_Nm,B.Base_Lev1 as Base_Lev1 ";
			sSQL=sSQL+" 							FROM TRAINFO A LEFT OUTER JOIN TRACONT2 B ON A.Pro_Id=B.Pro_Id ";
			sSQL=sSQL+"						WHERE A.Pro_Id=? ";
			sSQL=sSQL+" 						) XX ";
			sSQL=sSQL+" 			WHERE XX.Pro_Id=? AND XX.Pro_Id=ZZ.Pro_Id and XX.Rainfo_Id=ZZ.Rainfo_Id and ZZ.Base_Lev1=XX.Base_Lev1 and ZZ.Racont_Nm>XX.Racont_Nm) as idx ";
			sSQL=sSQL+" 	INTO #T1 ";
			sSQL=sSQL+" 	FROM ( ";
			sSQL=sSQL+"				SELECT A.Pro_Id ,A.Rainfo_Id,A.Rainfo_Nm,B.Racont_Nm AS Racont_Nm,B.Base_Lev1 as Base_Lev1 ";
			sSQL=sSQL+"					FROM TRAINFO A LEFT OUTER JOIN TRACONT1 B ON A.Pro_Id=B.Pro_Id AND A.Rainfo_id=B.Rainfo_Id ";
			sSQL=sSQL+"				 WHERE A.Pro_Id=? ";
			sSQL=sSQL+"				 UNION ALL";
			sSQL=sSQL+"				SELECT A.Pro_Id ,A.Rainfo_Id,A.Rainfo_Nm,B.Racont_Nm AS Racont_Nm,B.Base_Lev1 as Base_Lev1 ";
			sSQL=sSQL+"					FROM TRAINFO A LEFT OUTER JOIN TRACONT2 B ON A.Pro_Id=B.Pro_Id ";
			sSQL=sSQL+"				 WHERE A.Pro_Id=? ";
			sSQL=sSQL+"				) ZZ LEFT OUTER JOIN TAKIND YY ON ZZ.Pro_id=YY.Pro_Id AND ZZ.Base_Lev1=YY.Base_Lev1 ";
			sSQL=sSQL+"	WHERE YY.Pro_Id IS NOT NULL  ";
		    sSQL=sSQL+" SELECT ";
		    sSQL=sSQL+"   ZZ.Pro_Id, ";
		    sSQL=sSQL+"   ZZ.Rainfo_Id, ";
		    sSQL=sSQL+"   ZZ.Rainfo_Nm ";
		    if(listMap.size()!=0){
		    	sSQL=sSQL+",";
		    }
		    for(int i=0; i<listMap.size(); i++){
				MakindVO vo=(MakindVO)listMap.get(i);
				sSQL=sSQL+" (SELECT Racont_Nm FROM #T1 WHERE Pro_Id=ZZ.Pro_Id and Rainfo_Id=ZZ.Rainfo_Id and Base_Lev1='"+vo.getBase_lev1()+"' and Idx=ZZ.Idx) AS ak_id"+vo.getBase_lev1()+" ";
				if((listMap.size()-1)!=i){
					 sSQL=sSQL+",";
				}
			}
		    sSQL=sSQL+" FROM #T1 ZZ ";
		    sSQL=sSQL+" group by ZZ.Pro_Id ,ZZ.Rainfo_Id,ZZ.Rainfo_Nm,ZZ.Idx ";
		    sSQL=sSQL+" DROP TABLE #T1 ";
		    System.out.println(sSQL);
			//String sql="select * from barcode where userid=?";
			pstmt=con.prepareStatement(sSQL);
			pstmt.setInt(1, pro_id);
			pstmt.setInt(2, pro_id);
			pstmt.setInt(3, pro_id);
			pstmt.setInt(4, pro_id);
			pstmt.setInt(5, pro_id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				HashMap map=new HashMap();
				map.put("pro_id",rs.getInt(1));
				map.put("rainfo_id",rs.getString(2));
				map.put("rainfo_nm",rs.getString(3));
				 for(int i=0; i<listMap.size(); i++){
					MakindVO vo=(MakindVO)listMap.get(i);
					 map.put("ak_id"+vo.getBase_lev1(), rs.getString("ak_id"+vo.getBase_lev1()));
				 }
				 System.out.println("맵:"+map);
				 TreeMap tm = new TreeMap();
				 tm.putAll(map);
				 list.add(tm);
				//list.add(new ScmVO(rs.getString("bal_date"),rs.getString("bal_no"),rs.getString("p_no"),rs.getString("p_nm"),
				//		rs.getString("p_kd"),rs.getString("p_dan"),rs.getInt("bal_cnt"),rs.getString("ip_date"),rs.getString("bal_ok")));
				}
			}finally{
			closeAll(rs,pstmt,con);
		}
			System.out.println("리스트:"+list);
		return list;
	}
	
	
	//(자산위협매트릭스)
	@Override
	public void mridenty_insertTest(int pro_id, int ascu_id, int asc_id, String[] hiddenCode) throws SQLException {
		Connection con=null;
		Statement stmt=null;
		try{
			con=dataSource.getConnection();
			String sSQL="";
			for(int i=0;i<hiddenCode.length;i++){
				sSQL=sSQL+" insert into trisklist (risk_id, pro_id, raitem_id, tk_id, v_id,v_detail, cia_memo)" +
						" select (select rtrim(riskno) from atv_key("+pro_id+", (select raitem_id " +
								"from atv_select("+pro_id+","+ascu_id+","+asc_id+") where v_id " +
								"is not null and hiddenCode='"+hiddenCode[i]+"'))) as risk_id, pro_id,raitem_id, " +
								"tk_id, v_id, v_detail,cia_memo from atv_select("+pro_id+","+ascu_id+","+asc_id+") where v_id is not null and hiddenCode='"+hiddenCode[i]+"' ";
				/*
				sSQL=sSQL+" insert into ";
				sSQL=sSQL+"		 trisklist ";
				sSQL=sSQL+" 				(risk_id, ";
				sSQL=sSQL+" 				pro_id, ";
				sSQL=sSQL+" 				raitem_id, ";
				sSQL=sSQL+" 				tk_id, ";
				sSQL=sSQL+"  				v_id, ";
				sSQL=sSQL+" 				v_detail, ";
				sSQL=sSQL+" 				cia_memo) ";
				sSQL=sSQL+"  select ";
				sSQL=sSQL+"  		(select ";
				sSQL=sSQL+" 			rtrim(riskno)  ";
				sSQL=sSQL+"  		from atv_key("+pro_id+", ";
				sSQL=sSQL+"  		(select ";
				sSQL=sSQL+"  		 	raitem_id ";
				sSQL=sSQL+"  		from atv_select("+pro_id+","+ascu_id+","+asc_id+") ";
				sSQL=sSQL+"  		where v_id is not null and hiddenCode='"+hiddenCode[i]+"'))) as risk_id, ";
				sSQL=sSQL+"  		pro_id, ";
				sSQL=sSQL+"  		raitem_id, ";
				sSQL=sSQL+"  		tk_id, ";
				sSQL=sSQL+"  		v_id, ";
				sSQL=sSQL+"  		v_detail, ";
				sSQL=sSQL+"  		cia_memo ";
				sSQL=sSQL+"  from atv_select("+pro_id+","+ascu_id+","+asc_id+") ";
				sSQL=sSQL+"  where v_id is not null and hiddenCode='"+hiddenCode[i]+"' ";
				*/
			}
			
		    System.out.println(sSQL);
			//String sql="select * from barcode where userid=?";
			stmt=con.createStatement();
			stmt.executeUpdate(sSQL);
			//System.out.println("insert:"+result);
			}finally{
			closeAll(stmt,con);
		}
			//System.out.println("리스트:"+list);
	}
}
